import requests
from bs4 import BeautifulSoup
import time
import csv
import pymysql
from datetime import datetime

# 基础配置
BASE_URL = "http://www.air-level.com"
HEADERS = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'}
REQUEST_TIMEOUT = 10
DELAY = 1

# MySQL数据库配置
MYSQL_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '010615',
    'db': 'qxyt2',
    'charset': 'utf8mb4'
}


def create_mysql_connection():
    """创建MySQL数据库连接"""
    try:
        conn = pymysql.connect(**MYSQL_CONFIG)
        return conn
    except pymysql.Error as e:
        print(f"数据库连接失败：{str(e)}")
        return None


def create_table():
    """创建数据表"""
    conn = create_mysql_connection()
    if not conn:
        return

    create_table_sql = """
    CREATE TABLE IF NOT EXISTS aqi_data (
        id INT PRIMARY KEY AUTO_INCREMENT,
        city_name VARCHAR(100) NOT NULL,
        aqi_value VARCHAR(20),
        update_time DATETIME,
        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) CHARSET=utf8mb4
    """

    try:
        with conn.cursor() as cursor:
            cursor.execute(create_table_sql)
        conn.commit()
        print("数据表创建/验证成功")
    except pymysql.Error as e:
        print(f"创建表失败：{str(e)}")
    finally:
        conn.close()


def save_to_mysql(data):
    """保存数据到MySQL数据库"""
    conn = create_mysql_connection()
    if not conn:
        return

    update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    insert_sql = """
    INSERT INTO aqi_data 
    (city_name, aqi_value, update_time)
    VALUES (%s, %s, %s)
    """

    try:
        with conn.cursor() as cursor:
            # 清空旧数据
            cursor.execute("DELETE FROM aqi_data")
            # 批量插入新数据
            cursor.executemany(insert_sql,
                               [(item[0], item[1], update_time) for item in data])
        conn.commit()
        print(f"成功存储 {len(data)} 条数据到MySQL数据库")
    except pymysql.Error as e:
        print(f"数据库操作失败：{str(e)}")
        conn.rollback()
    finally:
        conn.close()


def get_all_cities():
    """获取所有城市链接及名称"""
    try:
        response = requests.get(BASE_URL, headers=HEADERS, timeout=REQUEST_TIMEOUT)
        response.raise_for_status()
        soup = BeautifulSoup(response.text, "html.parser")

        update_time = soup.find('h4').get_text(strip=True)
        print(f"数据更新时间：{update_time}")

        city_links = soup.select('#citylist a')
        cities = [(link.get_text(strip=True), link['href']) for link in city_links]

    except Exception as e:
        print(f"获取城市列表失败：{str(e)}")
        return []

    print(f"共发现 {len(cities)} 个城市")
    return cities


def get_city_aqi(city_name, city_url):
    """获取单个城市的AQI数据"""
    full_url = BASE_URL + city_url

    try:
        time.sleep(DELAY)
        response = requests.get(full_url, headers=HEADERS, timeout=REQUEST_TIMEOUT)
        response.raise_for_status()

        soup = BeautifulSoup(response.text, "html.parser")
        aqi_span = soup.find("span")

        if aqi_span and aqi_span.string:
            return (city_name, aqi_span.string.strip())
        return (city_name, "数据解析失败")

    except requests.exceptions.HTTPError as e:
        return (city_name, f"HTTP错误 {e.response.status_code}")
    except Exception as e:
        print(f"获取 {city_name} 数据时发生异常：{str(e)}")
        return (city_name, "获取失败")


def save_to_csv(data, filename="aqi_data.csv"):
    """保存数据到CSV文件"""
    with open(filename, 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)
        writer.writerow(["城市名称", "AQI值"])
        writer.writerows(data)
    print(f"数据已保存到 {filename}")


def main():
    # 初始化数据库
    create_table()

    cities = get_all_cities()
    if not cities:
        print("无法获取城市列表，程序终止")
        return

    aqi_data = []
    for index, (city_name, city_url) in enumerate(cities, 1):
        result = get_city_aqi(city_name, city_url)
        aqi_data.append(result)
        print(f"{index}/{len(cities)} {result[0]}: {result[1]}")

    # 保存数据
    save_to_csv(aqi_data)
    save_to_mysql(aqi_data)


if __name__ == "__main__":
    main()